Projections are specified by using the method query.Select(elements to project). The Select method is defined for both EntityQuery<T> and DynamicQuery. For EntityQuery<T> it creates a DynamicQuery of the entity query, using the projection specified, while Select() for a DynamicQuery simply defines the projection of the DynamicQuery. .Select() has a generic and non-generic variant. The generic variant returns a DynamicQuery<T>, the non-generic variant returns a DynamicQuery.
Elements to project is a list of 1 or more elements which can be assigned to or are equal to entity fields or constants. There are exceptions to this, see nested queries below.
For example a .Select(CustomerFields.CompanyName), will define a single field (Customer.CompanyName) in the projection. Expressions can be specified as well as an element to project. Expressions (Every IExpression implementing element) will be assigned to new entity fields.
To make life easier there is a placeholder defined which simply copies the complete projection of the query Select is called on. This object is added using the method Projection.Full. This is useful when calling Select() on an EntityQuery<T> in situations where the complete projection has to be re-used. For DynamicQuery instances, it has no effect and will lead to an error.
Example:
var q = qf.Customer
.Select(Projection.Full,
qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerUsingCustomerId)
.Sum(OrderFields.EmployeeId).As("TheSum"));
'q' will be a dynamic query over Customer (which will be wrapped in a
derived table), with the full projection of customer and also the scalar
query 'TheSum'.
If a field, expression or boolean representing predicate requires an alias, the element has to be appended with a .As(alias) call. The element is assigned the specified alias in the projection.
If no alias is specified and the element requires an alias, e.g. in the case of an expression or aggregated field, the element gets an automatic alias, LLBLV_number. Number is a unique number per query, increased with every element in the projection.
Nested queries are just queries like any other query, and can be an EntityQuery<T>, DynamicQuery<T> or DynamicQuery. A nested query has to have a correlated predicate, which is either specified with .CorrelatedOver(entity relation) or .CorrelatedOver(predicate). The predicate specified has to be a comparison between fields, so the nested query can be tied to the outer query's result-set. This is similar to the rules for nested queries in Linq.
Nested queries are specified in the projection of a dynamic query and if the outer query's projection they're defined in is a typed projection, the nested query also has to have a typed projection (EntityQuery<T> or DynamicQuery<T>).
To specify a predicate in the projection, e.g. through a Contains, All or Any call (which produce predicates), it's sufficient to simply specify the predicate. The QuerySpec system will wrap the predicate with an Functions.IIF(predicate, true, false) call. If no alias is specified, the default alias fme_number is used.
Typed projections are either an EntityQuery<T> or DynamicQuery<T> returned by a Select call. The lambda specified as argument for the Select() call is converted in a lambda for the WithProjector method, and a list of objects to pass to the untyped Select(params object[]) method.
A typed projection is also constructible by calling WithProjector manually. This gives fine grained control over what projection lambda is used and allows re-use of values in the raw set. The Select(lamdba) is easier to use, and the separated Select(params object[]) and WithProjector(lambda) combination gives more power.
To construct a result type, the elements in the projection have to be specified as a specific type. E.g. when the field CustomerFields.CompanyName is specified in the projection lambda, it is a field object, not a string, while the value it represents is a string. To specify the type, append .ToValue<type>() to the element, where type is the result type of the element.
Example:
qf.Create()
.Select(()=>new { CompanyName = CustomerFields.CompanyName.ToValue<string>() });
The example above defines a projection lambda and constructs an anonymous
type with one projection element: CustomerFields.CompanyName. This is stored
in the CompanyName property of the anonymous type during projection. The
.ToValue<string>() call signals the QuerySpec processor to handle the
CustomerFields.CompanyName element as a projection element. It also makes
sure the property CompanyName of the anonymous type is of type
string.
Under the hood, the above lambda is converted to the lambda:
r=>new { CompanyName = (string)r[0]};
and it will pass CustomerFields.CompanyName as the element to use in the
projection for the SQL query.
The above example also could have been specified as:
qf.Create()
.Select(CustomerFields.CompanyName)
.WithProjector(r=> new { CompanyName = (string)r[0]});
This separates projection of the query to execute on the database from
the projection to in-memory objects, which can be useful in some scenarios,
while it can be verbose in others. As both are equivalent, it depends on the
situation at hand which one to choose.
The following extension methods are available for specifying the element type in a typed projection lambda (the one passed to Select(lambda)) and on which types they're working on.
If an element doesn't have one of the calls above, e.g. you specify CustomerFields.CompanyName in the projection but not with .ToValue<string>(), it's not taken into account as an element to project from the resultset, and is compiled into the projection lambda together with the rest of the code inside the projection lambda. So in short:
// Incorrect
.Select(()=> new
{
CompanyName = CustomerFields.CompanyName,
CustomerId = CustomerFields.CustomerId
});
// correct
.Select(()=> new
{
CompanyName = CustomerFields.CompanyName.ToValue<string>(),
CustomerId = CustomerFields.CustomerId.ToValue<string>()
});
var qf = new QueryFactory();
var q = qf.Customer
.Where(CustomerFields.Country == "Germany")
.Select(() => new
{
CustomerId = CustomerFields.CustomerId.ToValue<string>(),
Orders = qf.Order
.CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)
.Select(() => new
{
OrderId = OrderFields.OrderId.ToValue<int>(),
OrderDate = OrderFields.OrderDate.ToValue<DateTime?>()
})
.ToResultset()
});
The example above has a nested query which fetches order information
(OrderId and OrderDate) of all orders of the customers from Germany,
directly into a resultset (A List<anonymoustype>) using the
ToResultset<T>() method.
This will result in two queries, one for the customers (the outer query) and one for the orders for all the customers matching the outer query. QuerySpec will then merge the two resultsets efficiently in-memory, avoiding a SELECT N+1 problem.
-- Query for customers.
SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId],
1 /* @p2 */ AS [LLBLV_1]
FROM [Northwind].[dbo].[Customers]
WHERE (((([Northwind].[dbo].[Customers].[Country] = 'Germany' /* @p3 */))))
-- Query for order data
SELECT [Northwind].[dbo].[Orders].[OrderID] AS [OrderId],
[Northwind].[dbo].[Orders].[OrderDate],
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId]
FROM [Northwind].[dbo].[Orders]
WHERE (((([Northwind].[dbo].[Orders].[CustomerID] IN
('ALFKI' /* @p1 */, 'BLAUS' /* @p2 */, 'DRACD' /* @p3 */, 'FRANK' /* @p4 */,
'KOENE' /* @p5 */, 'LEHMS' /* @p6 */, 'MORGK' /* @p7 */, 'OTTIK' /* @p8 */,
'QUICK' /* @p9 */, 'TOMSP' /* @p10 */, 'WANDK' /* @p11 */)))))